#!/usr/bin/env python
# coding=utf-8

# File Name: localUpdate.py
# Author: Bill Zhang
# Mail: zchcandid@gmail.com 
# Created Time: Tue 17 Dec 2013 09:33:59 AM CST
import pymongo
#mysql connector
import mysql.connector
from mysql.connector import errorcode

def conMongo(DataBase):
    uri="mongodb://hqjf:aa88OO00@192.168.100.58:8888/%s" % DataBase
    try:
        con=pymongo.MongoClient(uri)
    except pymongo.errors.ConnectionFailure:
        print("fail to connect mongodb,try after 3s")
    #        time.sleep(3)
        con=pymongo.MongoClient(uri)
    except pymongo.errors.InvalidURI:
        print("Your uri is not write,fail to connect mongodb!")
        print("The formate should be 'mongodb://user:password@example.com/the_database'")
        print("Please try again!")
    db=con.digikey
    return db
def conMysql(DataBase):
    try:
        #  cnx = mysql.connector.connect(user='root', password='123456',host='192.168.100.201',database=DataBase)
          cnx = mysql.connector.connect(user='admin001', password='admin001',host='192.168.100.58',database=DataBase)
    except mysql.connector.Error as err:
          if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
              print("Something is wrong with your user name or password")
          elif err.errno == errorcode.ER_BAD_DB_ERROR:
              print("Database does not exists")
          else:
              print(err)
    else:
        return cnx 
def Run(times,DataBase):
    """
    dataformate:
    {
        "_id" : ObjectId("529ef27022599e6f27f8d123"),
        "Q" : 0,
        "Q1" : 1,
        "TIME" : "2013-12-04 17:14:24",
        "URL" : "http://www.digikey.cn/product-detail/zh/F58000236/F58000236-ND/3538795",
        "P1" : 609.92,
        "HQID" : 910
    }
    """
    db=conMongo(DataBase)
    Mdb=conMysql(DataBase)
    CMdb=Mdb.cursor(buffered=True)
    CMdb1=Mdb.cursor(buffered=True)
    QPdata= db.update.find({"Q1":{"$gt":0}}).skip(times*1000).limit(100)
    if not QPdata:
        return 0
    for QP in QPdata:
        #print(QP)
        Mtable='ecs_goods_price_%s' % str(QP['HQID']%10)
        dQuery="DELETE FROM %s WHERE goods_id=%s" % (Mtable,str(QP['HQID']))
        d1Query="UPDATE ecs_goods SET goods_number=%s WHERE id=%s" % (str(int(QP['Q'])),str(QP['HQID']))
       # CMdb.execute(dQuery)
       # CMdb.execute(d1Query)
        if(QP['Q1']>0):
            iQuery="INSERT INTO %s (goods_id,purchases,unit_price) VALUES " % Mtable
            QPlen=int((len(QP.keys())-5)/2)+1
            for i in range(1,QPlen):   
                if ('P%s' % i) in QP: 
                    value=(QP['HQID'],QP['Q%s' % i],QP['P%s' % i])
                    iQuery+=(str(value)+',')
            iQuery=iQuery[0:-1]
            print(iQuery)
       #     CMdb1.execute(iQuery)
    return 1
       
if __name__=="__main__":
    """
    ---if __name=="__main__"-----
    This is for debug this moudle,
    When run this moudle as the main 
    the code below will run
    """
    times=534
    i=1
    while(i):
        DataBase='digikey'
        print(times)
        i= Run(times,DataBase)
        times+=1
        i=0
    print('Having been done!')

        
    
    
    




